package cl.whyem.appbase.coreutils;

import java.io.File;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

/**
 * Listener que tiene como objetivo ejecutar actualizaciones automaticas del
 * modelo/contenido de la BASE DE DATOS
 *
 * Para ello se ejecutan los scripts con forma XXXX.sql ubicados en el
 * directorio 'classpath:sql'
 *
 * @author whyem
 */
public class AutoDBPatcherListener implements ServletContextListener {

    private static final Logger LOGGER = LoggerFactory.getLogger(AutoDBPatcherListener.class);
    private static final String REGEX = "[0-9]*.sql";
    private static final String SQL_ROOT_PATH = "/sql";
    private WebApplicationContext springContext;
    private Connection connection;
    private DataSource dataSource;
    private File sqlPath;
    private List<String> patchList;

    public void contextInitialized(ServletContextEvent event) {
        LOGGER.info("---->>>> STARTUP DATABASE PATCHING PROCESS <<<<----");
        springContext = WebApplicationContextUtils.getRequiredWebApplicationContext(event.getServletContext());
        initProcesoPatcher();
        LOGGER.info("---->>>> ENDING DATABASE PATCHING PROCESS  <<<<----");
    }

    public void contextDestroyed(ServletContextEvent event) {
    }

    private void initProcesoPatcher() {
        try {
            configureInitialSetup();
            for (String patch : patchList) {
                if (!patch.matches(REGEX) || fueAplicado(connection, patch)) { continue; }
                try { ejecutarSQL(patch); } catch (Exception ex) { break; }
            }
        } catch (SQLException e) {
            LOGGER.error("SQL EXCEPTION: ", e);
        } catch (Exception e) {
            LOGGER.error("EXCEPTION: ", e);
        } finally {
            closeConnection(connection);
        }
    }

    private void ejecutarSQL(String patch) throws Exception {
        Connection conexionParche = null;
        try {
            conexionParche = getAutoCommitLessConnection();
            SQLExecuter executer = new SQLExecuter(conexionParche);
            executer.setSrc(new File(sqlPath + "/" + patch));
            executer.execute();
            marcarParcheComoAplicado(connection, patch);
        } catch (Exception exStoper) {
            LOGGER.error(String.format("FAIL STACKTRACE < %s > ", patch), exStoper);
            doRollBack(conexionParche);
            throw exStoper;
        } finally {
            closeConnection(conexionParche);
        }
    }

    private boolean fueAplicado(Connection connection, String patch) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT count(name) FROM sql_patches WHERE name LIKE ?");
        preparedStatement.setString(1, patch);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int contador = resultSet.getInt(1);
        resultSet.close();
        preparedStatement.close();
        return contador > 0;
    }

    private void marcarParcheComoAplicado(Connection connection, String patch) throws SQLException {
        LOGGER.info("SETTING PATCH AS APPLIED: {}", patch);
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO sql_patches VALUES(?)");
        preparedStatement.setString(1, patch);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }

    private List<String> cargarParchesDesdeDirectorio() {
        List<String> retorno = Collections.synchronizedList(new ArrayList<String>());
        for (File file : sqlPath.listFiles()) {
            retorno.add(file.getName());
        }
        Collections.sort(retorno);
        return retorno;
    }

    private boolean existeTablaDeParches(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        try {
            statement.executeQuery("SELECT * FROM sql_patches");
            return true;
        } catch (Throwable e) {
            return false;
        } finally {
            statement.close();
        }
    }

    private void crearTablaDeParches(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        String sql = "CREATE TABLE sql_patches (name VARCHAR(50) NOT NULL, CONSTRAINT PK_PATCHES PRIMARY KEY (name))";
        statement.execute(sql);
        statement.close();
    }

    private void doRollBack(Connection connection) throws SQLException {
        if (connection != null) {
            connection.rollback();
        }
    }

    private Connection getAutoCommitLessConnection() throws SQLException {
        Connection conexionSinAutoCommit = dataSource.getConnection();
        conexionSinAutoCommit.setAutoCommit(false);
        return conexionSinAutoCommit;
    }

    private File getFileFromURL() {
        URL url = this.getClass().getClassLoader().getResource(SQL_ROOT_PATH);
        File file = null;
        try {
            file = new File(url.toURI());
        } catch (URISyntaxException e) {
            file = new File(url.getPath());
        } finally {
            return file;
        }
    }

    private void configureInitialSetup() throws SQLException {
        dataSource = (DataSource) springContext.getBean("dataSource");
        connection = dataSource.getConnection();
        sqlPath = getFileFromURL();
        if (!existeTablaDeParches(connection)) {
            crearTablaDeParches(connection);
        }
        patchList = cargarParchesDesdeDirectorio();
    }

    private void closeConnection(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException ex) {
            LOGGER.error("ERROR CLOSING CONNECTION");
        }
    }

    //****** INNER CLASSES *******//
    
    private class SQLExecuter extends SQLExec {

        private Connection con;

        @Override
        public Connection getConnection() {
            return con;
        }

        public SQLExecuter(Connection con) {
            this.con = con;
            Project projectAux = new Project();
            projectAux.init();

            setProject(projectAux);
            setTaskType("sql");
            setTaskName("sql");
            setEncoding("UTF-8");
        }
    }
}